package com.kandinfo.base.security.repository;

import java.util.List;

import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.Query;

import com.kandinfo.base.admin.constant.CacheConstant;
import com.kandinfo.base.common.repository.BaseRepository;
import com.kandinfo.base.security.domain.User;

public interface UserRepository extends BaseRepository<User, Long> {

    @Cacheable(value = CacheConstant.USER_CACHE)
    User findUserById(Long id);

    User findByUsername(String username);
    
    @Query(value = "select count(1) from user where username = ?1", nativeQuery = true)
    Long countByUsername(String username);
    
    @Query(value = "select * from user where username = ?1 and mobile = ?2", nativeQuery = true)
    User findByUserNameAndMobile(String name, String mobile);

    @Query(value = "select * from user limit ?1, ?2", nativeQuery = true)
    List<User> getPage(int startId, int size);

    @Query(value = "select * from user where user_type= ?1", nativeQuery = true)
    List<User> findByType(int type);

    @Query(value = "select * from user where status=?1", nativeQuery = true)
    List<User> findActiveUser(String status);

    @Query(value = " select * from user where id in(select user_id from  user_role where role_id in "
            + " (SELECT role_id FROM role_perm " + " WHERE perm_id IN(SELECT id FROM perm WHERE perm_name=?1))) "
            + "and status ='A' or status = 'I'", nativeQuery = true)
    List<User> searchPermsers(String permname);

    @Query(value = " select u.* from user u " + " left join user_role ur on u.id = ur.user_id "
            + " inner join role r on r.id = ur.role_id and r.role_name = ?1"
            + " where u.status ='A' or u.status = 'I'", nativeQuery = true)
    List<User> searchSuperUsers(String director);

    @Query(value = "select distinct u.*  from user u inner join user_group ug on u.id = ug.user_id "
            + "where ug.group_id in (select ug1.group_id from user_group ug1 "
            + "where ug1.user_id = ?1) and u.status ='A' or u.status = 'I'", nativeQuery = true)
    List<User> getGroupUser(Long userId);

    @Query(value = "select distinct u.*  from user u inner join user_group ug on u.id = ug.user_id where ug.group_id =?1 and u.status ='A' or u.status = 'I'", nativeQuery = true)
    List<User> getUserByGroup(Long groupId);

    @Query(value = "select * from user where id in (select user_id from user_role where role_id in "
            + "(select id from role where role_name = ?1 or role_name=?2)) "
            + "and status ='A' or status = 'I'", nativeQuery = true)
    List<User> getSuperAdminAndDircetor(String superadmin, String dircetor);

    // @Query(value = "select * from user where name like ?1 or username like ?1 limit 10", nativeQuery = true)
    @Query(value = " select * from user u where  u.status ='A' or u.status = 'I'", nativeQuery = true)
    List<User> getUsersBykey();

    @Query(value = " select u.* from user u inner join user_info ui on u.id = ui.user_id and ui.user_type = ?1", nativeQuery = true)
    List<User> findByUserType(Integer userType);
}
